using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Data;
using DTOLayer;

namespace DAOLayer
{
    public class SachDAO : AbstractDao
    {
        public int TestExisted(SachDTO sdto)
        {
            int kq = -1;
            try
            {
                SqlConnection cn;
                cn = this.SqlConnectiondata();
                cn.Open();
                String str = "select count (*) from Sach Where tensach ='" + sdto.TenSach + "'";
                SqlCommand cmd = new SqlCommand(str, cn);
                if ((int)cmd.ExecuteScalar() == 0)
                    kq = 1;
                cn.Close();
                return kq;
            }
            catch (Exception e)
            {
                return kq;
            }
        }
        public int InsertSach(SachDTO sdto)
        {
            int kq = TestExisted(sdto);
            if (kq == -1)
            {
                return -1;
            }
            try
            {
                SqlConnection cn;
                cn = this.SqlConnectiondata();
                cn.Open();
                String str = "insert into Sach values ('" + sdto.TenSach + "'," + sdto.MaTheLoai + "," + sdto.MaTacGia + ",'";
                str += sdto.NamXuatBan + "'," + sdto.MaNhaXuatBan + "," + sdto.TinhTrang +")";
                SqlCommand cmd = new SqlCommand(str,cn);
                cmd.ExecuteNonQuery();
                cn.Close();
                return sdto.MaSach;
            }
            catch (Exception e)
            {
                return -1;
            }
        }
        public int DeleteSach(int masach)
        {
            try
            {
                SqlConnection cn;
                cn = this.SqlConnectiondata();
                cn.Open();
                String str = "delete from Sach where Masach = '" + masach + "'";
                SqlCommand cmd = new SqlCommand(str, cn);
                cmd.ExecuteNonQuery();
                cn.Close();
                return 1;
            }
            catch (Exception e)
            {
                return -1;
            }

        }
        public int UpdateSach(SachDTO sdto)
        {
            try
            {
                SqlConnection cn;
                cn = this.SqlConnectiondata();
                cn.Open();
                String str = "update Sach set tensach = '" + sdto.TenSach + "' , matheloai = '" + sdto.MaTheLoai + "' , matacgia = '" + sdto.MaTacGia + "' , namxuatban = '" + sdto.NamXuatBan + "' , manhaxuatban = '" + sdto.MaNhaXuatBan + "' , tinhtrang = '" + sdto.TinhTrang + "' where masach = " + sdto.MaSach;
                SqlCommand cmd = new SqlCommand(str, cn);
                cmd.ExecuteNonQuery();
                cn.Close();
                return 1;
            }
            catch(Exception e)
            {
                return -1;
            }
        }
        public DataTable LayDanhSach()
        {
            DataTable dt = new DataTable();
            try
            {
                SqlConnection cn;
                cn = this.SqlConnectiondata();
                cn.Open();
                String str = "select * from Sach";
                
                SqlDataAdapter d = new SqlDataAdapter(str,cn);
                d.Fill(dt);
                cn.Close();
                
            }
            catch (Exception e)
            {
                return null;
            }
            return dt;
        }
        public DataTable LayDanhSachTheoTinhTrang(string TT)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlConnection cn;
                cn = this.SqlConnectiondata();
                cn.Open();
                String str = "select * from Sach where TinhTrang='"+TT+"'";
                SqlDataAdapter d = new SqlDataAdapter(str, cn);
                d.Fill(dt);
                cn.Close();

            }
            catch (Exception e)
            {
                return null;
            }
            return dt;
        }
        public DataTable LaySach(int ma)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlConnection cn;
                cn = this.SqlConnectiondata();
                cn.Open();
                String str = "select * from Sach S,TacGia T,NhaXuatBan N ,TheLoaiSach TL where S.MaTacGia=T.MatacGia and S.MaNhaXuatBan=N.MaNhaXuatBan and S.maTheLoai=TL.MaTheLoai and S.TinhTrang='false' and S.MaSach="+ma;
                SqlCommand cmd = new SqlCommand(str, cn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                SqlDataAdapter d = new SqlDataAdapter(str, cn);
                d.Fill(dt);
                cn.Close();

            }
            catch (Exception e)
            {
                return null;
            }
            return dt;
        }
        public int CapNhatTinhTrang(int ma,string tt)
        {
            try
            {
                SqlConnection cn;
                cn = this.SqlConnectiondata();
                cn.Open();
                String str = "Update Sach Set TinhTrang='"+ tt+ "' where MaSach="+ma;
                SqlCommand cmd = new SqlCommand(str, cn);
                cmd.ExecuteNonQuery();
                cn.Close();
                return 1;

            }
            catch (Exception e)
            {
                return -1;
            }
        }
    }
}
